package com.pj.lib.auraServer.components.DB;


import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Vector;
import java.util.Map.Entry;


import com.pj.lib.auraServer.app.Server;
import com.pj.lib.auraServer.app.properties.DBProperties;
import com.pj.lib.auraServer.components.DB.tables.TableInterface;
import com.pj.lib.auraServer.components.DB.tables.PG.PGAuraType;
import com.pj.lib.auraServer.components.DB.tables.PG.PGUser;
import com.pj.lib.auraServer.components.DB.tables.PG.PGUserLocation;
import com.pj.lib.auraServer.components.DB.tables.PG.PGUserState;
import com.pj.lib.auraServer.components.DB.tables.PG.PGUserStateType;
import com.pj.lib.auraServer.components.connection.NetworkManager;
import com.pj.lib.auraServer.exceptions.AuraServerException;
import com.pj.lib.auraServer.exceptions.NoSuchDBException;
import com.pj.lib.auraServer.exceptions.NoSuchDBTypeException;
import com.pj.lib.auraServer.utils.Log;

public class PostgresDB extends DB{
	
	
	private static PostgresDB db;
	private PostgresDB(DBProperties properties) throws AuraServerException {
		super(properties, getDefaultTableInstances());
	}
	private PostgresDB(DBProperties properties, Properties tablesInstances ) {
		super(properties, tablesInstances);
	}

	@Override
	public void connect() throws ClassNotFoundException, NoSuchDBException,
			SQLException {
		String url = "jdbc:postgresql://" + 
			properties.getProperty(DBProperties.KEY_URL) + ":" + 
			properties.getProperty(DBProperties.KEY_PORT) + "/" + 
			properties.getProperty(DBProperties.KEY_SCHEMA);
		Class.forName("org.postgresql.Driver");
	  
		conn = (java.sql.Connection) DriverManager.getConnection(url, properties);
		
		Server.debug("+ server connected to database");
	}

	public static PostgresDB getInstance() throws AuraServerException{
		if(db == null) {
			throw new AuraServerException("First you have to init DB");
		} else {
			return db;
		}
	}
	
	public static PostgresDB getInstance(DBProperties properties) throws AuraServerException {

		if(db != null) {

			return PostgresDB.getInstance();
		} else {
			db = new PostgresDB(properties);
			db.setDBForTables();
			return db;
		}
	}
	
	public static PostgresDB getInstance(DBProperties properties, Properties tableInstances) throws AuraServerException {
		if(db != null) {
			return PostgresDB.getInstance();
		} else {
			db = new PostgresDB(properties, tableInstances);
			db.setDBForTables();
		}
		return db;
	}

	@Override
	public ArrayList<Map<String, Object>> get(String sql, Object[] params) throws AuraServerException, SQLException {
		if(isConnected()) {
			PreparedStatement st = conn.prepareStatement(sql);
			
			if(params != null) {
				int i = 1;
				
				for(Object param : params) {
					if(param instanceof Integer) {
						st.setInt(i, ((Number) param).intValue());
					} else if(param instanceof Long) {
						st.setLong(i, ((Number) param).longValue());
					} else if(param instanceof Double) {
						st.setDouble(i, ((Number) param).doubleValue());
					} else if(param instanceof Date) {
						st.setDate(i, (java.sql.Date) param);
					} else if(param instanceof String) {
						st.setString(i, param.toString());
					} else {
						if(param == null) {
							st.setObject(i, param);
						} else {
							throw new NoSuchDBException("No such class type: " + param.getClass().getName());
						}
					}
					i++;
				}
			}
			
			ResultSet rs = st.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			
			int numCols = rsmd.getColumnCount();
			
			ArrayList<Map<String, Object>> resultArray = new ArrayList<Map<String, Object>>();
			HashMap<String, Object> rowMap;
			
			while(rs.next()){
			
				rowMap = new HashMap<String, Object>();
				for(int j = 1; j<=numCols; j++) {
					
					if(rsmd.getColumnClassName(j).equals("java.lang.Integer")) {
						rowMap.put(rsmd.getColumnName(j).toUpperCase(), rs.getInt(j));
					} else if(rsmd.getColumnClassName(j).equals("java.lang.Long")) {
						rowMap.put(rsmd.getColumnName(j).toUpperCase(), rs.getLong(j));
					} else if(rsmd.getColumnClassName(j).equals("java.lang.Double")) {
						rowMap.put(rsmd.getColumnName(j).toUpperCase(), rs.getDouble(j));
					} else if(rsmd.getColumnClassName(j).equals("java.util.Date")) {
						rowMap.put(rsmd.getColumnName(j).toUpperCase(), rs.getDate(j));
					} else if(rsmd.getColumnClassName(j).equals("java.lang.String")) {
						rowMap.put(rsmd.getColumnName(j).toUpperCase(), rs.getString(j));
					} else if(rsmd.getColumnClassName(j).equals("java.lang.Boolean")) {
						rowMap.put(rsmd.getColumnName(j).toUpperCase(), rs.getBoolean(j));
					} else if(rsmd.getColumnClassName(j).equals("java.math.BigDecimal")) {
						rowMap.put(rsmd.getColumnName(j).toUpperCase(), rs.getBigDecimal(j).intValue());
					} else if(rsmd.getColumnClassName(j).equals("java.lang.Float")) {
						rowMap.put(rsmd.getColumnName(j).toUpperCase(), rs.getFloat(j));
					} else {
						
						throw new AuraServerException("No such class type when getting data from ResultType: " + rsmd.getColumnClassName(j));
					}
				}
				resultArray.add(rowMap);
			}
			rs.close();
			st.close();
			return resultArray;
		} else {
			throw new AuraServerException("There is no connection");
		}

	}

	@Override
	public ArrayList<Map<String, Object>> get(String sql, List<Object> params) throws AuraServerException, SQLException {
		return get(sql, params.toArray());
	}
	
	@Override
	public ArrayList<Map<String, Object>> get(String sql) throws AuraServerException,
			SQLException {
		return get(sql, new LinkedList());
	}
	@Override
	public int update(String sql, Object[] params) throws AuraServerException,
			SQLException {
		if(isConnected()) {
			PreparedStatement st = conn.prepareStatement(sql);

			
			if(params != null) {
				int i = 1;
				for(Object param : params) {
					if(param instanceof Integer) {
						st.setInt(i, ((Number) param).intValue());
					} else if(param instanceof Long) {
						st.setLong(i, ((Number) param).longValue());
					} else if(param instanceof Double) {
						st.setDouble(i, ((Number) param).doubleValue());
					} else if(param instanceof Date) {
						st.setDate(i, (java.sql.Date) param);
					} else if(param instanceof String) {
						st.setString(i, param.toString());
					} else if(param instanceof Float) {
						st.setFloat(i, ((Float) param).floatValue());
					} else {
						if(param == null) {
							st.setObject(i, param);
						} else {
							throw new NoSuchDBTypeException("No such class type: " + param.getClass().getName());
						}
					}
					i++;
				}
			}

			int rowsAffected =  st.executeUpdate();
			
			st.close();
			return rowsAffected;
		} else {
			throw new AuraServerException("There is no connection");
		}
		
	}

	@Override
	public int update(String sql, List<Object> params)
			throws AuraServerException, SQLException {
		return update(sql, params.toArray());
		
	}
	
	@Override
	public int update(String sql) throws AuraServerException, SQLException{
		return update(sql, new LinkedList());
	}
	
	@Override
	public void exec(String sql) throws AuraServerException, SQLException {
		if(isConnected()) {
			Statement st = conn.createStatement();
			st.execute(sql);
			st.close();
		} else {
			throw new AuraServerException("There is no connection");
		}
		
	}
	@Override
	public void createDB() throws AuraServerException, SQLException  {
		Log.i(this, "Checking if tables for database are created and if necessary creating...");
		try{
			//starting transaction
			transactionStart();
			
			ArrayList<Map<String, Object>> results = this.get("select * from pg_tables where schemaname='public';");			
			
			//checking lib_users
			((PGUser)getInstance(DB.INSTANCE_USER)).createIfNotExists(results);
			
			
			//checking lib_user_locations
			((PGUserLocation)getInstance(DB.INSTANCE_USER_LOCATION)).createIfNotExists(results);
			
			
			//checking lib_aura_type
			((PGAuraType)getInstance(DB.INSTANCE_AURA_TYPE)).createIfNotExists(results);
			
			//checking lib_user_states
			((PGUserState)getInstance(DB.INSTANCE_USER_STATE)).createIfNotExists(results);
			
			//checking lib_user_state_types
			((PGUserStateType)getInstance(DB.INSTANCE_USER_STATE_TYPE)).createIfNotExists(results);
			
			
			
			//checking lib_users
			((PGUser)getInstance(DB.INSTANCE_USER)).afterCreate();
			
			
			//checking lib_user_locations
			((PGUserLocation)getInstance(DB.INSTANCE_USER_LOCATION)).afterCreate();
			
			
			//checking lib_aura_type
			((PGAuraType)getInstance(DB.INSTANCE_AURA_TYPE)).afterCreate();
			
			//checking lib_user_states
			((PGUserState)getInstance(DB.INSTANCE_USER_STATE)).afterCreate();
			
			//checking lib_user_state_types
			((PGUserStateType)getInstance(DB.INSTANCE_USER_STATE_TYPE)).afterCreate();
			
			//commiting transaction
			transactionCommit();
		} catch(AuraServerException e) {
			//rollback transaction if error
			transactionRollback();
			throw e;
		} catch(SQLException e) {
			//rollback transaction if error
			transactionRollback();
			throw e;
		}
		Log.i(this, "Tables for database have been created");
	}
	
	/*
	 * All db must have this method
	 */
	public static Properties getDefaultTableInstances() throws AuraServerException {
		Properties insts = new Properties();
		insts.put(DB.INSTANCE_USER, PGUser.getInstance());
		insts.put(DB.INSTANCE_USER_LOCATION, PGUserLocation.getInstance());
		insts.put(DB.INSTANCE_AURA_TYPE, PGAuraType.getInstance());
		insts.put(DB.INSTANCE_USER_STATE, PGUserState.getInstance());
		insts.put(DB.INSTANCE_USER_STATE_TYPE, PGUserStateType.getInstance());
		
		
		return insts;
	}
	
	private static void setDBForTables() throws AuraServerException{
		Iterator<Entry<Object, Object>> it =  getDefaultTableInstances().entrySet().iterator();

		
		while(it.hasNext()) {
			Map.Entry<Object, Object> pairs = (Map.Entry<Object, Object>) it.next();
			((TableInterface)pairs.getValue()).setDB(PostgresDB.getInstance());
		}
	}

}
